const express = require('express');
// const roomRouter = require('./routes/room');
const cors = require('cors');
const app = express();
const port = 3000;
const mysql = require('mysql');
const dayjs = require('dayjs');
const multer = require('multer');
const ExcelJS = require('exceljs');
const fs = require('fs');
const path = require('path');
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, 'express/public/images'); // 上传文件保存的目录
  },
  filename: function (req, file, cb) {
    cb(null, file.originalname); // 使用原始文件名作为保存的文件名
  },
});

const upload = multer({ storage: storage });
// const upload = multer({ dest: 'express/public/images' });

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'patrol_db',
});
connection.connect();

// const bodyParser = require('body-parser');
// app.use(bodyParser.urlencoded({ extended: true }));
app.use(cors());
app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));
// app.use('/room', roomRouter);
app.get('/', (req, res) => {
  res.send('Hello World!');
});

// 上传图片
app.post('/upload', upload.single('files'), (req, res) => {
  const file = req.file;
  // 检查是否有文件上传
  if (!file) {
    res.status(400).send('没有文件上传');
    return;
  }

  // 生成文件地址的数组
  // const fileList = req.files.map((file) => {
  //   return {
  //     name: file.originalname,
  //     url: `http://localhost:3000/images/${file.filename}`,
  //   };
  // });
  // 返回文件地址数组给前端
  res.json({
    name: file.originalname,
    url: `http://localhost:3000/images/${file.filename}`,
  });
});

// 增加机房
app.post('/addRoom', (req, res) => {
  const { pid, room_name, created_by, updated_by } = req.body;
  const query = `INSERT INTO room (pid, room_name, created_by, updated_by) VALUES ('${pid}','${room_name}', '${created_by}', '${updated_by}')`;
  connection.query(query, (err, result) => {
    if (err) {
      console.log(err);
      res.status(500).send('Error adding room');
    } else {
      res.status(200).send('Room added successfully');
    }
  });
});

// 修改机房
app.post('/editRoom', (req, res) => {
  const { room_name, created_by, updated_by, room_id } = req.body;
  // 在数据库中更新机房记录
  const query = `UPDATE room SET room_name = ?, created_by = ?, updated_by = ? WHERE id = ?`;
  connection.query(
    query,
    [room_name, created_by, updated_by, room_id],
    (error, results, fields) => {
      if (error) throw error;
      res.send(`ID 为 ${room_id} 的机房记录已更新`);
    }
  );
});

// 增加设备
app.post('/addDevice', (req, res) => {
  const device = req.body;
  console.log(device, 'device');
  connection.query(
    'INSERT INTO device SET ?',
    device,
    (error, results, fields) => {
      if (error) {
        console.error(error);
        res.status(500).send('Error adding device');
      } else {
        res.send('Device added successfully');
      }
    }
  );
});

// 新增巡检记录
app.post('/addInspection', (req, res) => {
  const { inspection_time, inspector } = req.body;
  const querySql = `SELECT COUNT(*) as count FROM inspections WHERE inspector = ? AND inspection_date = ?`;
  connection.query(querySql, [inspector, inspection_time], (err, results) => {
    if (err) throw err;
    const count = results[0].count;
    if (count > 0) {
      res.status(400).send('巡检记录已存在');
    } else {
      const query = `INSERT INTO inspections (inspection_date, inspector) VALUES (?, ?)`;
      connection.query(query, [inspection_time, inspector], (err, results) => {
        if (err) throw err;
        const newInspectionId = results.insertId;
        const getNewInspectionQuery = `SELECT * FROM inspections WHERE inspection_id = ?`;
        connection.query(
          getNewInspectionQuery,
          [newInspectionId],
          (err, newInspection) => {
            if (err) throw err;
            res.send(newInspection[0]);
          }
        );
      });
    }
  });
});

// 保存巡检数据
app.post('/saveInspection', (req, res) => {
  const { inspector, inspection_time, inspections } = req.body;
  // 检查机房id是否已存在
  const query = `SELECT COUNT(*) as count FROM inspections_form WHERE inspection_time = ? AND inspector = ? AND room_id = ?`;
  const sql = `INSERT INTO inspections_form (inspection_id, room_id,device_id, inspector, inspection_time, device_status, device_fault_reason, img_list) VALUES ?`;
  const values = inspections.map((inspection) => [
    inspection.inspection_id,
    inspection.room_id,
    inspection.device_id,
    inspection.inspector,
    inspection.inspection_time,
    inspection.device_status,
    inspection.device_fault_reason,
    inspection.img_list,
  ]);
  connection.query(
    query,
    [inspection_time, inspector, values[0][1]],
    (err, results) => {
      if (err) throw err;
      const count = results[0].count;
      if (count > 0) {
        res.status(400).send('设备记录已存在');
      } else {
        connection.query(sql, [values], (err, result) => {
          if (err) throw err;
          console.log('Inspection data saved');
          res.send('Inspection data saved');
        });
      }
    }
  );
});

//更新某条巡检记录的设备状态
app.post('/editInspection', (req, res) => {
  const { inspection_id, inspections } = req.body;
  // 更新设备状态信息
  inspections.forEach((device) => {
    const { device_status, device_fault_reason, img_list, device_id } = device;
    const updateQuery = `UPDATE inspections_form SET device_status = ?,device_fault_reason = ?, img_list = ? WHERE inspection_id = ? AND device_id = ?`;
    connection.query(
      updateQuery,
      [device_status, device_fault_reason, img_list, inspection_id, device_id],
      (err, results) => {
        if (err) throw err;
      }
    );
  });
  res.send('巡检设备状态已更新');
});

// 查询巡检记录
app.get('/searchInspections', (req, res) => {
  const { inspector, inspection_date } = req.query;
  let query = `SELECT * FROM inspections`;

  // 构建查询条件
  let conditions = [];
  let params = [];

  if (inspector) {
    conditions.push('inspector LIKE ?');
    params.push(`%${inspector}%`);
  }

  if (inspection_date) {
    conditions.push('inspection_date = ?');
    params.push(inspection_date);
  }

  if (conditions.length > 0) {
    query += ' WHERE ' + conditions.join(' AND ');
  }

  connection.query(query, params, (err, results) => {
    if (err) throw err;
    results.map((item) => {
      item.inspection_date = dayjs(item.inspection_date).format('YYYY-MM-DD');
    });
    res.send(results);
  });
});

// 根据巡检记录ID查询表单记录的GET路由处理程序
app.get('/getInspectionForm/:id', (req, res) => {
  const inspectionId = req.params.id;

  // 执行查询操作
  const query = `SELECT * FROM inspections_form WHERE inspection_id = ?`;
  connection.query(query, [inspectionId], (err, results) => {
    if (err) throw err;

    // 检查是否有查询结果
    if (results.length === 0) {
      res.send([]);
    } else {
      res.send(results);
    }
  });
});

app.get('/exceptionDevices/:inspection_id/:device_status', (req, res) => {
  const inspectionId = req.params.inspection_id;
  const deviceStatus = req.params.device_status;

  let query = `
    SELECT r.room_name , d.name AS device_name, d.factory_name, d.telephone, i.*
    FROM inspections_form AS i
    JOIN room AS r ON i.room_id = r.id
    JOIN device AS d ON i.device_id = d.id
    WHERE i.inspection_id = ? 
  `;
  const params = [inspectionId];
  if (deviceStatus) {
    query += 'AND i.device_status = ?';
    params.push(deviceStatus);
  }

  connection.query(query, params, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

app.post('/deleteInspection', (req, res) => {
  const { inspection_id } = req.body;
  const formQuery = 'DELETE FROM inspections_form WHERE inspection_id = ?';
  connection.query(formQuery, [inspection_id], (err, results) => {
    if (err) throw err;
    const deleteQuery = 'DELETE FROM inspections WHERE inspection_id = ?';
    connection.query(deleteQuery, [inspection_id], (err, results) => {
      if (err) throw err;
      res.send('巡检记录和关联设备数据已删除');
    });
  });
});

app.get('/exportInspectionData/:inspection_id/:device_status', (req, res) => {
  // 创建一个新的工作簿
  const workbook = new ExcelJS.Workbook();
  // 创建一个新的工作表
  const worksheet = workbook.addWorksheet('巡检数据');

  // 添加表头
  worksheet.columns = [
    { header: '位置', key: 'room_name', width: 20 },
    { header: '设备名称', key: 'device_name', width: 20 },
    { header: '故障描述', key: 'device_fault_reason', width: 20 },
    { header: '故障图片', key: 'img_list', width: 50 },
    { header: '维护单位', key: 'factory_name', width: 20 },
    { header: '联系单位', key: 'telephone', width: 20 },
  ];

  // 在工作表中添加数据
  // worksheet.addRow(['位置', '设备名称', '故障描述', '维护单位', '联系单位']);

  // 从数据库中获取巡检数据并添加到工作表中
  const inspectionId = req.params.inspection_id;
  const deviceStatus = req.params.device_status;

  let query = `
    SELECT r.room_name , d.name AS device_name, d.factory_name, d.telephone, i.*
    FROM inspections_form AS i
    JOIN room AS r ON i.room_id = r.id
    JOIN device AS d ON i.device_id = d.id
    WHERE i.inspection_id = ? 
  `;
  const params = [inspectionId];
  if (deviceStatus) {
    query += 'AND i.device_status = ?';
    params.push(deviceStatus);
  }
  connection.query(query, params, (err, results) => {
    if (err) throw err;
    results.forEach((row) => {
      worksheet.addRow([
        row.room_name,
        row.device_name,
        row.device_fault_reason,
        row.img_list,
        row.factory_name,
        row.telephone,
      ]);
    });

    // 将工作簿保存为Excel文件
    workbook.xlsx
      .writeFile('inspection_data.xlsx')
      .then(() => {
        // 将Excel文件发送给前端
        res.download('inspection_data.xlsx', 'inspection_data.xlsx', (err) => {
          if (err) throw err;
          // 删除临时文件
          fs.unlink('inspection_data.xlsx', (err) => {
            if (err) throw err;
          });
        });
      })
      .catch((err) => {
        throw err;
      });
  });
});

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`);
});
